package com.sf.dlb.jpa.repository;

import com.sf.dlb.jpa.model.VideoRecordInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 视频播放记录表 Repository
 *
 * Created by jing.tan on 2018/5/21.
 */
@Repository
public interface VideoRecordRepository extends JpaRepository<VideoRecordInfo, Long> {
    /**
     * 根据用户Id查询
     * @return
     */
    List<VideoRecordInfo> findByUserIdAndDelFlag(String userId,String delFlag);
    /**
     * 根据用户Id,视频Id查询
     * @return
     */
    VideoRecordInfo findByUserIdAndVideoIdAndDelFlag(String userId,String videoId,String delFlag);
    /**
     * 查询视频记录
     * @param userId
     * @param createDate
     * @param delFlag
     * @return
     */
    @Query(value = "SELECT * FROM video_record a WHERE user_id=?1 AND a.del_flag=?2 AND DATE_FORMAT(a.create_date,'%Y-%m-%d')=?3",nativeQuery = true)
    List<VideoRecordInfo> findByUserIdAndCreateDateAndDelFlag(String userId,String createDate,String delFlag);
    /**
     * 每日视频数量-状态(有效)
     * @param userId
     * @param time
     * @param delflag
     * @param getState
     * @return
     */
    @Query(value = "select count(*) from video_record a where a.user_id=?1 and DATE_FORMAT(a.start_time,'%Y-%m-%d')=?2 and a.del_flag=?3 and a.get_state=?4",nativeQuery = true)
    Integer findByEveryDayVideoNumber(String userId, String time, String delflag, String getState);
    /**
     * 每日视频数量
     * @param userId
     * @param time
     * @param delflag
     * @return
     */
    @Query(value = "select count(*) from video_record a where a.user_id=?1 and DATE_FORMAT(a.start_time,'%Y-%m-%d')=?2 and a.del_flag=?3",nativeQuery = true)
    Integer findByEveryDayVideoNumber(String userId, String time, String delflag);
    /**
     * 每日视频挖宝-dlb
     * @param userId
     * @param time
     * @param delflag
     * @return
     */
    @Query(value = "select sum(cast(a.get_number as decimal(18,3))) from video_record a where a.user_id=?1 and DATE_FORMAT(a.start_time,'%Y-%m-%d')=?2 and a.get_state=?3 and a.del_flag=?4 ",nativeQuery = true)
    Double getEveryDayVideoGetdlb(String userId, String time, String getState, String delflag);
}